package br.com.northwind.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.northwind.database.Conexao;
import br.com.northwind.exceptions.ConexaoException;
import br.com.northwind.exceptions.DriverException;
import br.com.northwind.fabricas.FabricaConexao;
import br.com.northwind.interfaces.IProductsDao;
import br.com.northwind.tabelas.Products;

public class ProductsDao implements IProductsDao {

	private final String SQL_INSERT = "INSERT INTO Products (ProductName, SupplierId, CategoryId, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private final String SQL_UPDATE = "UPDATE Products SET ProductName = ?, SupplierId = ?, CategoryId = ?, QuantityPerUnit = ?, UnitPrice = ?, UnitsInStock = ?, UnitsOnOrder = ?, ReorderLevel = ?, Discontinued = ? WHERE ProductId = ?";
	private final String SQL_DELETE = "DELETE FROM Products WHERE ProductId = ?";
	private final String SQL_SELECT_TODOS = "SELECT * FROM Products";
	private final String SQL_SELECT_BY_CODIGO = "SELECT * FROM Products WHERE ProductId = ?";
	
	private Conexao conexao;
	private PreparedStatement statement = null;
	private ResultSet rs = null;

	public ProductsDao() {
		this.conexao = FabricaConexao.criarConexao();
	}

	public ProductsDao(Conexao conexao) {
		this.conexao = conexao;
	}

	public void inserir(Products _products) throws ConexaoException,DriverException {
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_INSERT);
			
			this.statement.setString(1, _products.getProductName());
			this.statement.setInt(2, _products.getSupplierId());
			this.statement.setInt(3, _products.getCategoryId());
			this.statement.setString(4, _products.getQuantityPerUnit());
			this.statement.setDouble(5, _products.getUnitPrice());
			this.statement.setInt(6, _products.getUnitsInStock());
			this.statement.setInt(7, _products.getUnitsOnOrder());
			this.statement.setInt(8, _products.getReorderLevel());
			this.statement.setBoolean(9, _products.isDiscontinued());
			
			this.statement.execute();
		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
	}

	public void editar(Products _products) throws ConexaoException,DriverException {
		PreparedStatement update = null;
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_BY_CODIGO);
			
			this.statement.setInt(1, _products.getProductId());
			
			this.rs = statement.executeQuery();

			if (rs.next()) {
				update = this.conexao.getConexao().prepareStatement(this.SQL_UPDATE);

				update.setString(1, _products.getProductName());
				update.setInt(2, _products.getSupplierId());
				update.setInt(3, _products.getCategoryId());
				update.setString(4, _products.getQuantityPerUnit());
				update.setDouble(5, _products.getUnitPrice());
				update.setInt(6, _products.getUnitsInStock());
				update.setInt(7, _products.getUnitsOnOrder());
				update.setInt(8, _products.getReorderLevel());
				update.setBoolean(9, _products.isDiscontinued());

				update.execute();
				update.close();
			}
		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				if (this.rs != null) this.rs.close();
				if (update != null) update.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}

	}

	public void excluir(Integer _productid) throws ConexaoException,DriverException {
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_DELETE);
			
			this.statement.setInt(1, _productid);
			
			this.statement.execute();

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				if (this.rs != null) this.rs.close();
				
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
	}

	public List<Products> selecionarAll() throws ConexaoException,DriverException {
		List<Products> listaDeProducts = new ArrayList<Products>();
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_TODOS);
			this.rs = this.statement.executeQuery();

			while (rs.next()) {
				
				int ProductId = this.rs.getInt(1);
				String ProductName = this.rs.getString(2);
				int SupplierId = this.rs.getInt(3);
				int CategoryId = this.rs.getInt(4);
				String QuantityPerUnit = this.rs.getString(5);
				double UnitPrice = this.rs.getDouble(6);
				int UnitsInStock = this.rs.getInt(7);
				int UnitsOnOrder = this.rs.getInt(8);
				int ReorderLevel = this.rs.getInt(9);
				boolean Discontinued = this.rs.getBoolean(10);
				
				listaDeProducts.add(new Products(ProductId, ProductName, SupplierId, CategoryId, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued));
				
			}

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
		return listaDeProducts;
	}

	public Products selecionarByCod(Integer _productid) throws ConexaoException, DriverException {
		Products localizado = null;
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_BY_CODIGO);
			this.statement.setInt(1, _productid);
			this.rs = this.statement.executeQuery();

			if (rs.next()) {
				
				int ProductId = this.rs.getInt(1);
				String ProductName = this.rs.getString(2);
				int SupplierId = this.rs.getInt(3);
				int CategoryId = this.rs.getInt(4);
				String QuantityPerUnit = this.rs.getString(5);
				double UnitPrice = this.rs.getDouble(6);
				int UnitsInStock = this.rs.getInt(7);
				int UnitsOnOrder = this.rs.getInt(8);
				int ReorderLevel = this.rs.getInt(9);
				boolean Discontinued = this.rs.getBoolean(10);
				
				localizado = new Products(ProductId, ProductName, SupplierId, CategoryId, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued);
				
			}

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
		return localizado;
	}

}
